package datasource;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

public enum ConnectionSQLite3 {

	datasource;
	
	private Connection con = null; //连接对象
	
	private String databaseName = "test";//数据库名称
	
	private static final Logger logger = LogManager.getLogger(ConnectionSQLite3.class);
	
	private ConnectionSQLite3() {}
	
	/**
	 * 默认是test
	 * @return
	 * @author yutao
	 * @date 2017年5月15日下午5:26:00
	 */
	public Connection getConnectionSQL(){
		preConnection(databaseName);
		return this.con;
	}
	
	/**
	 * 可以指定数据库名称
	 * @param databaseName
	 * @return
	 * @author yutao
	 * @date 2017年5月15日下午5:25:45
	 */
	public Connection getConnectionSQL(String databaseName){
		preConnection(databaseName);
		return this.con;
	}


	/**
	 * 获取连接对象
	 * @param name
	 * @author yutao
	 * @date 2017年5月15日下午5:22:29
	 */
	private void preConnection(String name) {
		try {
			Class.forName("org.sqlite.JDBC");
			File file = new File("");
			String absolutePath = file.getAbsolutePath();//当前路径的绝对路径
			
			String fileName = absolutePath + "/db";
			File fileDB = new File(fileName);
			if(!fileDB.exists()){
				boolean mkdirs = fileDB.mkdirs();
				logger.debug("---sqlite3---创建数据库文件夹是否成功-----" + mkdirs);
			}
			con = DriverManager.getConnection("jdbc:sqlite:db/" + name + ".db");//连接数据库
			logger.info("Opened database successfully");
			if(con != null){
				init(con);//去初始化几张表
			}
		} catch (Exception e) {
			logger.error( e.getClass().getName() + ": " + e.getMessage() );
		}
	}

	
	/**
	 * 初始化必备表
	 * @param connection
	 * @throws SQLException
	 * @author yutao
	 * @date 2017年5月19日下午3:54:45
	 */
	private void init(Connection connection) throws SQLException {
		connection.setAutoCommit(false);
		boolean iscodeShell = isTableExist("codeshell", connection);
		
		Statement st = connection.createStatement();
		if(!iscodeShell){
			String codeShellSql = "create table codeshell " +
								  "(id INTEGER primary key autoincrement," +
								  "project_name char(50) not null UNIQUE," +
								  "code_shell text)";

			st.executeUpdate(codeShellSql);// 创建脚本表
		}
		
		boolean islogShell = isTableExist("log", connection);
		if(!islogShell){
			String logSql = "create table log "+
							"(id INTEGER primary key autoincrement," +
							"createtime datetime not null," +
							" project_name char(50) not null)";
			
			st.executeUpdate(logSql);//创建日志表
		}
		st.close();
	}
	
	/**
	 * 判断表是否存在
	 * @param name
	 * @param connection
	 * @return true存在，false不存在
	 * @throws SQLException
	 * @author yutao
	 * @date 2017年5月19日下午4:43:22
	 */
	public static boolean isTableExist(String name, Connection connection) throws SQLException{
		//先去判断表是否存在
		String tableSql = "select count(*) count from sqlite_master where type='table' and name = ?";
		
		PreparedStatement ps = connection.prepareStatement(tableSql);
		ps.setString(1, name);
		
		ResultSet rs = ps.executeQuery();
		
		boolean isExists = true;
		
		while(rs.next()){
			if( 0 == rs.getInt(1)){//说明需要初始化，表不存在
				isExists = false;
			}
		}
		rs.close();
		ps.close();
		return isExists;
	}
}
